-- @owner: lihongji
-- @date: 2022/07/28
-- @testpoint: create table as select

--step1:建序列;expect:成功
drop sequence if exists sq_ustore_regr_intercept_0021;
create sequence sq_ustore_regr_intercept_0021 increment by 1 start with 10;

--step2:建表并插入数据;expect:成功
drop table if exists t_ustore_regr_intercept_0021 cascade;
create table t_ustore_regr_intercept_0021(
 col_1 integer,
 col_2 number(19,0),
 col_3 number(10,6)
) with (storage_type=ustore);

begin
 for i in 1..50 loop
  insert into t_ustore_regr_intercept_0021 values(
  i,
  sq_ustore_regr_intercept_0021.nextval,
  i+445.255
  );
 end loop;
end;
/

--step3:建表;expect:成功
drop table if exists t_ustore_regr_intercept_0021_1;
create table t_ustore_regr_intercept_0021_1 with (storage_type=ustore) as select regr_intercept(col_2,col_2) as a from t_ustore_regr_intercept_0021 group by col_1,col_2,col_3;

--step4:查询;expect:成功
select * from t_ustore_regr_intercept_0021_1 order by 1 limit 10;

--step5:建表;expect:成功
drop table if exists t_ustore_regr_intercept_0021_2;
create table t_ustore_regr_intercept_0021_2 with (storage_type=ustore) as select regr_intercept(col_2-col_3,col_2-col_3) as a from t_ustore_regr_intercept_0021;

--step6:查询;expect:成功
select * from t_ustore_regr_intercept_0021_2 order by 1;

--step7:清理环境;expect:成功
drop table if exists t_ustore_regr_intercept_0021_1;
drop table if exists t_ustore_regr_intercept_0021_2;
drop sequence if exists sq_ustore_regr_intercept_0021;
drop table if exists t_ustore_regr_intercept_0021 cascade;